﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace QuanLyShopCD
{
    class AcessData
    {
        private SqlConnection con = new SqlConnection();

        public SqlConnection GetConn
        {
            get { return con; }
        }

        public AcessData() {
            con.ConnectionString = "Data Source=FIN;Initial Catalog=shopcd;Integrated Security=True";
        }

        public void Open()
        {
            if(con.State == ConnectionState.Closed)
                con.Open();
        }
        public void Close()
        {
            if (con.State == ConnectionState.Open)
                con.Close();
        }

        public DataTable Get_DataTable(String SQL)
        {
            SqlDataAdapter adap = new SqlDataAdapter(SQL,con);
            DataTable tb = new DataTable();
            adap.Fill(tb);
            return tb;
        }

        public DataSet Get_DataSet(ref DataSet ds,String SQL)
        {
            DataTable tb = Get_DataTable(SQL);
            //DataSet ds = new DataSet();
            ds.Tables.Add(tb);
            return ds;
        }

        /// <summary>
        /// phuong thuc Execute_SQL thực thi lệnh
        /// vấn tin SQL
        /// </summary>
        /// <param name="SQL">Lệnh SQL(insert into,Delete, Update)</param>


        public void Execute_SQL(String SQL)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = SQL;//kieu thuc hien sql la text
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
        }

        public DataTable Execute_ps(string Name_PS)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = Name_PS;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            DataTable tb = new DataTable();
            adp.Fill(tb);
            return tb;
        }
        public DataTable Execute_ps(string Name_PS, SqlParameter[] pr)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = Name_PS;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddRange(pr);
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            DataTable tb = new DataTable();
            adp.Fill(tb);
            return tb;

        }

    }
}
